package webExperiment;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.Date;
import java.util.Properties;

/**
 * O. Lindemann Version 1.21
 */
public class SQLHandler {
    private static final long serialVersionUID = 1L;
    private Connection db = null;
    private String lastCommand = "";
    private String tmpCmd = "";
    private BufferedReader br;
    private ResultSet result;
    private ConnectionDetails details = new ConnectionDetails("", "", "", "");
    private int lastInsertID = 0;

    public static class ConnectionDetails {
	public String server;
	public String database;
	public String user;
	public String password;

	public ConnectionDetails(String theServer, String theDatabase,
		String theUser, String thePassword) {
	    set(theServer, theDatabase, theUser, thePassword);
	}

	public void set(String theServer, String theDatabase, String theUser,
		String thePassword) {
	    server = theServer;
	    database = theDatabase;
	    user = theUser;
	    password = thePassword;
	}

	public void loadFromFile(URL fileURL) throws IOException {
	    Properties p = new Properties();
	    InputStream in = fileURL.openStream();
	    p.load(in);
	    password = p.getProperty("SQLpassword").trim();
	    server = p.getProperty("SQLserver").trim();
	    database = p.getProperty("SQLdatabase").trim();
	    user = p.getProperty("SQLuser").trim();
	}
    }

    public void setConnectionDetails(URL SQLiniFile) throws IOException {
	ConnectionDetails sqlDetails = new SQLHandler.ConnectionDetails("", "",
		"", "");
	sqlDetails.loadFromFile(SQLiniFile);
	setConnectionDetails(sqlDetails);
    }

    public void setConnectionDetails(ConnectionDetails theConnectionDetails) {
	setConnectionDetails(theConnectionDetails.server,
		theConnectionDetails.database, theConnectionDetails.user,
		theConnectionDetails.password);
    }

    public void setConnectionDetails(String theServer, String theDatabase,
	    String theUser, String thePassword) {
	details.set(theServer, theDatabase, theUser, thePassword);
	disconnect();
    }

    public ConnectionDetails getDetails() {
	return details;
    }

    public ResultSet getResult() {
	return result;
    }

    public String getLastCommand() {
	return lastCommand;
    }

    public Connection getDB() {
	return db;
    }

    public int getLastInsertID() {
	return lastInsertID;
    }

    public void connect() throws ClassNotFoundException, SQLException {
	// Load the JDBC driver
	final String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC
	// driver
	Class.forName(driverName);
	// Create a connection to the database
	String url = "jdbc:mysql://" + details.server + "/" + details.database; // a
	// JDBC
	// url
	db = DriverManager.getConnection(url, details.user, details.password);
	/*
	 * catch (ClassNotFoundException e) { // Could not find the database
	 * driver writeLine("Could not find the database driver"=; } catch
	 * (SQLException e) { // Could not connect to the database
	 * writeLine("Could not connect to the database"); }
	 */
    }

    public int setSQLFile(File SQLFile) throws IOException {
	// returns fileLength
	LineNumberReader lnr = new LineNumberReader(new FileReader(SQLFile));
	while (lnr.readLine() != null)
	    ;
	int fileLength = lnr.getLineNumber();
	lnr.close();
	if (br != null)
	    br.close();
	br = new BufferedReader(new FileReader(SQLFile));
	tmpCmd = "";
	return fileLength;
    }

    public int setSQLFile(URL fileURL) throws IOException {
	// returns fileLength
	InputStreamReader in = new InputStreamReader(fileURL.openStream());
	LineNumberReader lnr = new LineNumberReader(in);
	while (lnr.readLine() != null)
	    ;
	int fileLength = lnr.getLineNumber();
	lnr.close();
	in.close();
	if (br != null)
	    br.close();
	br = new BufferedReader(new InputStreamReader(fileURL.openStream()));
	tmpCmd = "";
	return fileLength;
    }

    private boolean fileProcessNextLine() throws IOException { // if ";" execute
	// command
	int x;
	String ln;
	if ((ln = br.readLine()) != null) {
	    if (ln.length() >= 2)
		if (!ln.substring(0, 2).equalsIgnoreCase("--")) {
		    tmpCmd = tmpCmd + ln;
		    x = tmpCmd.lastIndexOf(';');
		    if (x > 0) {
			try {
			    execute(tmpCmd.substring(0, x));
			} catch (SQLException e) {
			    e.printStackTrace();
			    System.out.println(e.getMessage());
			}
			tmpCmd = tmpCmd.substring(x + 1);
		    }
		}
	    return true;
	}// line
	else
	    // EOF
	    return false;
    }

    public int select(String SQLCommand) throws SQLException { // return noRows
	Statement queryStatement = db.createStatement();
	lastCommand = "SELECT " + SQLCommand;
	result = queryStatement.executeQuery(lastCommand);
	result.last();
	int noRows = result.getRow();
	result.beforeFirst();
	return noRows;
    }

    public boolean doesTableExist(String tablename) throws SQLException {
	Statement queryStatement = db.createStatement();
	lastCommand = "show tables like '" + tablename + "';";
	result = queryStatement.executeQuery(lastCommand);
	result.last();
	int noRows = result.getRow();
	return (noRows > 0);
    }

    public boolean execute(String SQLCommand) throws SQLException {
	Statement executeStatement = db.createStatement();
	lastCommand = SQLCommand;
	return executeStatement.execute(lastCommand);
    }

    public int insertInto(String table, String variables, String values)
	    throws SQLException {
	execute("INSERT INTO `" + table + "` (" + trimComma(variables)
		+ ") VALUES (" + values + ");");
	select("LAST_INSERT_ID();");
	getResult().next();
	lastInsertID = getResult().getInt("LAST_INSERT_ID()");
	return lastInsertID;
    }

    public boolean update(String table, String updates, String where)
	    throws SQLException {
	return execute("UPDATE `" + table + "` SET " + trimComma(updates)
		+ " WHERE " + where);
    }

    public boolean createTable(String table, String variables,
	    String primaryKeyName) throws SQLException {
	// <primaryKeyName> will be also added (in old version this was always
	// "id")
	String cmd = "CREATE TABLE `" + table + "` (";
	cmd = cmd + "`" + primaryKeyName
		+ "` int(11) unsigned NOT NULL auto_increment,";
	cmd = cmd + trimComma(variables);
	cmd = cmd + ",  PRIMARY KEY  (`" + primaryKeyName + "`) ";
	cmd = cmd + ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
	return execute(cmd);
    }

    public boolean dropTable(String tab) throws SQLException {
	return execute("DROP TABLE IF EXISTS `" + tab + "`;");
    }

    public void exportResultToTxt(File outputFile) throws IOException,
	    SQLException {
	// save sql result to text file
	final String sep = "\t";
	FileWriter st = new FileWriter(outputFile);
	ResultSetMetaData meta = result.getMetaData();
	int noCol = meta.getColumnCount();
	for (int i = 1; i <= noCol; i++) {
	    st.write(meta.getColumnName(i) + sep);
	}
	st.write("\n");

	result.beforeFirst();
	while (result.next()) {
	    for (int i = 1; i <= noCol; i++) {
		st.write(result.getString(i) + sep);
	    }
	    st.write("\n");
	}
	st.close();
	result.beforeFirst();
    }

    public static String trimComma(String str)
    // removes last comma if exsist
    {
	str = str.trim();
	if (str.endsWith(","))
	    str = str.substring(0, str.length() - 1);
	return str;
    }

    public static String sqlTime(Date now) {
	java.sql.Time sqlTime = new Time(now.getTime());
	return sqlTime.toString();
    }

    public static String sqlDate(Date now) {
	java.sql.Date sqlDate = new java.sql.Date(now.getTime());
	return sqlDate.toString();
    }

    public void disconnect() {
	if (db != null)
	    try {
		db.close();
	    } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }
    }

    public int processSQLFile(URL file) throws IOException {
	if (file == null)
	    return 0;
	int fileLength = setSQLFile(file);
	boolean EOF = true;
	while (EOF) {
	    EOF = fileProcessNextLine();
	}
	return fileLength;
    }

}
